package cn.gson.crm.model.dao;

import java.sql.Date;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import cn.gson.crm.model.Jdbc;
import cn.gson.crm.model.entity.Accessory;
import cn.gson.crm.vo.InterviewVo;

public class AccessoryDao {
	/**
	 * 保存附件成功后返回该ID
	 * @param accessory
	 * @return
	 * @throws SQLException
	 */
	public Long saveAccessory(Accessory accessory) throws SQLException{
		String sql = "insert accessory(file_name,file_path,file_size,model,upload_person) values(?,?,?,?,?)";
		//如果保存成功就返回最新一条记录的ID
		if(Jdbc.getInstace().executeUpdate(sql, accessory.getFileName(),accessory.getFilePath(),accessory.getFileSize(),accessory.getModel(),accessory.getUploadPerson())){
			String sql2 = "select id from accessory order by id desc limit 1";
			Map<String,Object> map = Jdbc.getInstace().executeQuery(sql2);
			return Long.parseLong(map.get("id").toString());
		}
		return null;
	}
	
	/**
	 * 关联所属记录
	 * @param interviewId
	 * @param attchs
	 * @throws SQLException
	 */
	public void guanLian(Long interviewId, String[] attchs) throws SQLException {
		String sql = "update accessory set record = ? where id = ? ";
		for (String aid : attchs) {
			Jdbc.getInstace().executeUpdate(sql, interviewId, aid);
		}
	}
	
	/**
	 * 根据ID进行 删除附件操作
	 * @param id
	 * @return
	 * @throws SQLException
	 */
	public boolean deleteAccessory(int id) throws SQLException{
		String sql = "delete from accessory where id = ?";
		return Jdbc.getInstace().executeUpdate(sql, id);
	}
	
	/**
	 * 查询所有面试信息
	 * @param interview
	 * @return
	 * @throws SQLException
	 */
	public List<InterviewVo> getAllInterview() throws SQLException{
		String sql = "select m.*,( " +
					"select count(1) from accessory a where a.record = m.view_id  and a.model='interview' " +
					") accessory from interview m order by interview_time desc";
		List<Map<String,Object>> map = Jdbc.getInstace().executeQueryForList(sql);
		List<InterviewVo> list = new ArrayList<>();
		for (Map<String,Object> vo : map) {
			InterviewVo iv  = new InterviewVo();
			iv.setViewId(Integer.parseInt(vo.get("view_id").toString()));
			iv.setStuId(Integer.parseInt(vo.get("stu_id").toString()));
			iv.setCompanyName(vo.get("company_name").toString());
			iv.setCompanyAddress(vo.get("company_address").toString());
			iv.setInterviewTime(java.sql.Date.valueOf(vo.get("interview_time").toString()));
			iv.setInterviewSummary(vo.get("interview_summary").toString());
			iv.setAccessory((Long)vo.get("accessory"));
			
			list.add(iv);
		}
		return list;
	}
	
	/**
	 * 查询签约收款文件信息
	 * @param record
	 * @param model
	 * @return
	 * @throws SQLException
	 */
	public List<Accessory> getAllAccessoryByModel(int personId,String model) throws SQLException{
		String sql = "select * from accessory where upload_person = ? and model = ?";
		List<Map<String,Object>> map = Jdbc.getInstace().executeQueryForList(sql,personId,model);
		List<Accessory> list = new ArrayList<>();
		for (Map<String,Object> a : map) {
			Accessory accessory = new Accessory();
			accessory.setFileName((a.get("file_name").toString()));
			accessory.setFilePath((a.get("file_path").toString()));
			accessory.setUploadTime(Timestamp.valueOf(a.get("upload_time").toString()));
			
			list.add(accessory);
		}
		return list;
	}
	/**
	 * 查询谁上传的记录并查看在那个模块上传的附件
	 * @param record
	 * @param model
	 * @return
	 * @throws SQLException
	 */
	public List<Accessory> getAllAccessoryByModelAndRecord(int record,String model) throws SQLException{
		String sql = "select * from accessory where record = ? and model = ?";
		List<Map<String,Object>> map = Jdbc.getInstace().executeQueryForList(sql, record,model);
		List<Accessory> list = new ArrayList<>();
		for (Map<String,Object> a : map) {
			Accessory accessory = new Accessory();
			accessory.setFileName((a.get("file_name").toString()));
			accessory.setFilePath((a.get("file_path").toString()));
			accessory.setUploadTime(Timestamp.valueOf(a.get("upload_time").toString()));
			
			list.add(accessory);
		}
		return list;
	}
}
